package com.guoli.sms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.guoli.sms.dao.IUserPhoneDao;
import com.guoli.sms.util.DBTool;
import com.guoli.sms.vo.Dept;
import com.guoli.sms.vo.UserPhone;

/**
 * @author zhuxiaodong
 * 
 */
public class UserPhoneDaoImpl implements IUserPhoneDao {

	private DBTool dbTool = DBTool.newInstance();

	@Override
	public List<UserPhone> getUserPhones(Connection conn) throws SQLException {
		String sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		List<UserPhone> userPhones = new ArrayList<UserPhone>();
		while (rs.next()) {
			UserPhone userPhone = new UserPhone();
			userPhone.setId(rs.getInt(1));
			userPhone.setU_name(rs.getString(2));
			userPhone.setU_phoneno(rs.getString(3));
			userPhone.setU_phonepswd(rs.getString(4));
			userPhone.setU_phonemeid(rs.getString(5));
			userPhone.setU_phonetype(rs.getString(6));
			userPhone.setU_remark(rs.getString(7));
			userPhone.setDept(new Dept(rs.getInt(8)));
			userPhone.setU_company(rs.getString(9));
			userPhones.add(userPhone);
		}
		dbTool.closeResultSet(rs);
		dbTool.closePstmt(pstmt);
		return userPhones;
	}

	@Override
	public List<UserPhone> getUserPhonesByDept(int dept_id, Connection conn) throws SQLException {
		String sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, u_company from t_b_userphone where ref_d_id=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, dept_id);
		ResultSet rs = pstmt.executeQuery();
		List<UserPhone> userPhones = new ArrayList<UserPhone>();
		while (rs.next()) {
			UserPhone userPhone = new UserPhone();
			userPhone.setId(rs.getInt(1));
			userPhone.setU_name(rs.getString(2));
			userPhone.setU_phoneno(rs.getString(3));
			userPhone.setU_phonepswd(rs.getString(4));
			userPhone.setU_phonemeid(rs.getString(5));
			userPhone.setU_phonetype(rs.getString(6));
			userPhone.setU_remark(rs.getString(7));
			userPhone.setU_company(rs.getString(8));
			userPhone.setDept(new Dept(dept_id));
			userPhones.add(userPhone);
		}
		dbTool.closeResultSet(rs);
		dbTool.closePstmt(pstmt);
		return userPhones;
	}

	@Override
	public UserPhone getUserPhonesById(int id, Connection conn) throws SQLException {
		String sql = "select u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where id=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		UserPhone userPhone = null;
		if (rs.next()) {
			userPhone = new UserPhone();
			userPhone.setId(id);
			userPhone.setU_name(rs.getString(1));
			userPhone.setU_phoneno(rs.getString(2));
			userPhone.setU_phonepswd(rs.getString(3));
			userPhone.setU_phonemeid(rs.getString(4));
			userPhone.setU_phonetype(rs.getString(5));
			userPhone.setU_remark(rs.getString(6));
			userPhone.setDept(new Dept(rs.getInt(7)));
			userPhone.setU_company(rs.getString(8));
		}
		dbTool.closeResultSet(rs);
		dbTool.closePstmt(pstmt);
		return userPhone;
	}

	@Override
	public List<UserPhone> getUserPhonesLikeName(String name, Connection conn) throws SQLException {
		String sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_name like ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, name);
		ResultSet rs = pstmt.executeQuery();
		List<UserPhone> userPhones = new ArrayList<UserPhone>();
		while (rs.next()) {
			UserPhone userPhone = new UserPhone();
			userPhone.setId(rs.getInt(1));
			userPhone.setU_name(rs.getString(2));
			userPhone.setU_phoneno(rs.getString(3));
			userPhone.setU_phonepswd(rs.getString(4));
			userPhone.setU_phonemeid(rs.getString(5));
			userPhone.setU_phonetype(rs.getString(6));
			userPhone.setU_remark(rs.getString(7));
			userPhone.setDept(new Dept(rs.getInt(8)));
			userPhone.setU_company(rs.getString(9));
			userPhones.add(userPhone);
		}
		dbTool.closeResultSet(rs);
		dbTool.closePstmt(pstmt);
		return userPhones;
	}

	@Override
	public void updateUserPhone(UserPhone userPhone, Connection conn) throws SQLException {
		String sql = "update t_b_userphone set u_name=?, u_phoneno=?, u_phonepswd=?, u_phonemeid=?, u_phonetype=?, u_remark=?, ref_d_id=?, u_company=? where id=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, userPhone.getU_name());
		pstmt.setString(2, userPhone.getU_phoneno());
		pstmt.setString(3, userPhone.getU_phonepswd());
		pstmt.setString(4, userPhone.getU_phonemeid());
		pstmt.setString(5, userPhone.getU_phonetype());
		pstmt.setString(6, userPhone.getU_remark());
		pstmt.setInt(7, userPhone.getDept().getId());
		pstmt.setString(8, userPhone.getU_company());
		pstmt.setInt(9, userPhone.getId());
		pstmt.executeUpdate();
		dbTool.closePstmt(pstmt);
	}

	@Override
	public void addUserPhone(UserPhone userPhone, Connection conn) throws SQLException {
		String sql = "insert into t_b_userphone(u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company) values(?,?,?,?,?,?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, userPhone.getU_name());
		pstmt.setString(2, userPhone.getU_phoneno());
		pstmt.setString(3, userPhone.getU_phonepswd());
		pstmt.setString(4, userPhone.getU_phonemeid());
		pstmt.setString(5, userPhone.getU_phonetype());
		pstmt.setString(6, userPhone.getU_remark());
		pstmt.setInt(7, userPhone.getDept().getId());
		pstmt.setString(8, userPhone.getU_company());
		pstmt.executeUpdate();
		dbTool.closePstmt(pstmt);
	}

	@Override
	public UserPhone getUserPhonesByNo(String no, Connection conn) throws SQLException {
		String sql = "select id, u_name, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_phoneno=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, no);
		ResultSet rs = pstmt.executeQuery();
		UserPhone userPhone = null;
		if (rs.next()) {
			userPhone = new UserPhone();
			userPhone.setId(rs.getInt(1));
			userPhone.setU_name(rs.getString(2));
			userPhone.setU_phoneno(no);
			userPhone.setU_phonepswd(rs.getString(3));
			userPhone.setU_phonemeid(rs.getString(4));
			userPhone.setU_phonetype(rs.getString(5));
			userPhone.setU_remark(rs.getString(6));
			userPhone.setDept(new Dept(rs.getInt(7)));
			userPhone.setU_company(rs.getString(8));
		}
		dbTool.closeResultSet(rs);
		dbTool.closePstmt(pstmt);
		return userPhone;
	}

	@Override
	public List<UserPhone> getUserPhonesByDN(int dept_id, String u_name, Connection conn) throws SQLException {
		String sql = "";
		PreparedStatement pstmt = null;
		if(dept_id == 0){
			sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_name like ? and u_name <> ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+u_name+"%");
			pstmt.setString(2, "");
		}else if(dept_id == 1){
			sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_name is null or u_name =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "");
		}else{
			sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_name like ? and ref_d_id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+u_name+"%");
			pstmt.setInt(2, dept_id);
		}
		ResultSet rs = pstmt.executeQuery();
		List<UserPhone> userPhones = new ArrayList<UserPhone>();
		while (rs.next()) {
			UserPhone userPhone = new UserPhone();
			userPhone.setId(rs.getInt(1));
			userPhone.setU_name(rs.getString(2));
			userPhone.setU_phoneno(rs.getString(3));
			userPhone.setU_phonepswd(rs.getString(4));
			userPhone.setU_phonemeid(rs.getString(5));
			userPhone.setU_phonetype(rs.getString(6));
			userPhone.setU_remark(rs.getString(7));
			userPhone.setDept(new Dept(rs.getInt(8)));
			userPhone.setU_company(rs.getString(9));
			userPhones.add(userPhone);
		}
		dbTool.closeResultSet(rs);
		dbTool.closePstmt(pstmt);
		return userPhones;
	}

	@Override
	public void delUserPhone(int u_id, Connection conn) throws SQLException {
		String sql = "delete from t_b_userphone where id = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, u_id);
		pstmt.executeUpdate();
		dbTool.closePstmt(pstmt);
	}

	@Override
	public List<UserPhone> getUserPhonesByDNCS(int dept_id, String u_name, String u_company, String u_phoneno, Connection conn) throws SQLException {
		String sql = "";
		PreparedStatement pstmt = null;
		if(dept_id == 0 && "0".equals(u_company)){
			sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_name like ? and u_name <> ? and u_phoneno like ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+u_name+"%");
			pstmt.setString(2, "");
			pstmt.setString(3, "%"+u_phoneno+"%");
		}else if(dept_id == 0 && !"0".equals(u_company)){
			sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_name like ? and u_name <> ? and u_phoneno like ? and u_company=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+u_name+"%");
			pstmt.setString(2, "");
			pstmt.setString(3, "%"+u_phoneno+"%");
			pstmt.setString(4, u_company);
		}else if(dept_id == 1){
			sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where (u_name = ? or u_name is null or ref_d_id = ?) and u_phoneno like ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "");
			pstmt.setInt(2, 1);
			pstmt.setString(3, "%"+u_phoneno+"%");
		}else if(dept_id!=0 && "0".equals(u_company)){
			sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_name like ? and ref_d_id=? and u_phoneno like ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+u_name+"%");
			pstmt.setInt(2, dept_id);
			pstmt.setString(3, "%"+u_phoneno+"%");
		}else{
			sql = "select id, u_name, u_phoneno, u_phonepswd, u_phonemeid, u_phonetype, u_remark, ref_d_id, u_company from t_b_userphone where u_name like ? and ref_d_id=? and u_phoneno like ? and u_company=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+u_name+"%");
			pstmt.setInt(2, dept_id);
			pstmt.setString(3, "%"+u_phoneno+"%");
			pstmt.setString(4, u_company);
		}
		ResultSet rs = pstmt.executeQuery();
		List<UserPhone> userPhones = new ArrayList<UserPhone>();
		while (rs.next()) {
			UserPhone userPhone = new UserPhone();
			userPhone.setId(rs.getInt(1));
			userPhone.setU_name(rs.getString(2));
			userPhone.setU_phoneno(rs.getString(3));
			userPhone.setU_phonepswd(rs.getString(4));
			userPhone.setU_phonemeid(rs.getString(5));
			userPhone.setU_phonetype(rs.getString(6));
			userPhone.setU_remark(rs.getString(7));
			userPhone.setDept(new Dept(rs.getInt(8)));
			userPhone.setU_company(rs.getString(9));
			userPhones.add(userPhone);
		}
		dbTool.closeResultSet(rs);
		dbTool.closePstmt(pstmt);
		return userPhones;
	}

	@Override
	public List<String> getCompanyNames(Connection conn) throws SQLException {
		String sql = "select distinct u_company from t_b_userphone where u_company <> ? and u_company is not null";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, "");
		ResultSet rs = pstmt.executeQuery();
		List<String> compNames = new ArrayList<String>();
		while(rs.next()){
			compNames.add(rs.getString(1));
		}
		return compNames;
	}

}
